/*
 * PartDao.java
 *
 */
package com.sun.demo.addressbook.db;

import com.sun.demo.addressbook.Part;
import com.sun.demo.addressbook.PartListEntry;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Connection;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

/**
 *
 */
public class PartDao {
    private LppDbSetup lppDbSetup;
    private Connection dbConnection;
    private boolean isConnected;
    private PreparedStatement stmtCreatePart;
    private PreparedStatement stmtUpdatePart;
    private PreparedStatement stmtGetPart;
    private PreparedStatement stmtDeletePart;
    
    public PartDao() {
        lppDbSetup = LppDbSetup.getLppDbSetup();
        System.out.println(lppDbSetup.getDatabaseDiskLocation());
        System.out.println(lppDbSetup.getDatabaseUrl());
    }
    
    public boolean connect() {
        String dbUrl = lppDbSetup.getDatabaseUrl();
        try {
            dbConnection = DriverManager.getConnection(dbUrl, lppDbSetup.getDatabaseProperties());
            stmtCreatePart = dbConnection.prepareStatement(strCreatePart, Statement.RETURN_GENERATED_KEYS);
            stmtUpdatePart = dbConnection.prepareStatement(strUpdatePart);
            stmtGetPart = dbConnection.prepareStatement(strGetPart);
            stmtDeletePart = dbConnection.prepareStatement(strDeletePart);
            isConnected = true;
        } catch (SQLException ex) {
            isConnected = false;
            ex.printStackTrace();
        }
        return isConnected;
    }
    
    public void disconnect() {
        if(isConnected) {
            try {
                dbConnection.commit();
                dbConnection.close();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            isConnected = false;
        }
    }
    
    public int createPart(Part record) {
        int id = -1;
        try {
            stmtCreatePart.clearParameters();
            
            stmtCreatePart.setString(1, record.getPartName());
            stmtCreatePart.setBoolean(2, record.getIsSpeakingPart());
            stmtCreatePart.setBoolean(3, record.getIsSingingPart());
            stmtCreatePart.setString(4, record.getNotes());
            int rowCount = stmtCreatePart.executeUpdate();
            ResultSet results = stmtCreatePart.getGeneratedKeys();
            if (results.next()) {
                id = results.getInt(1);
            }
            
        } catch(SQLException sqle) {
            sqle.printStackTrace();
        }
        return id;
    }
    
    public boolean editPart(Part record) {
        boolean bEdited = false;
        try {
            stmtUpdatePart.clearParameters();
            
            stmtUpdatePart.setString(1, record.getPartName());
            stmtUpdatePart.setBoolean(2, record.getIsSpeakingPart());
            stmtUpdatePart.setBoolean(3, record.getIsSingingPart());
            stmtUpdatePart.setString(4, record.getNotes());
            stmtUpdatePart.setInt(5, record.getId());
            stmtUpdatePart.executeUpdate();
            bEdited = true;
        } catch(SQLException sqle) {
            sqle.printStackTrace();
        }
        return bEdited;
    }
    
    public boolean deletePart(int id) {
        boolean bDeleted = false;
        try {
            stmtDeletePart.clearParameters();
            stmtDeletePart.setInt(1, id);
            stmtDeletePart.executeUpdate();
            bDeleted = true;
        } catch (SQLException sqle) {
            sqle.printStackTrace();
        }
        return bDeleted;
    }
    
    public boolean deletePart(Part record) {
        int id = record.getId();
        return deletePart(id);
    }
    
    public List<PartListEntry> getListEntries() {
        List<PartListEntry> listEntries = new ArrayList<PartListEntry>();
        Statement queryStatement = null;
        ResultSet results = null;
        
        try {
            queryStatement = dbConnection.createStatement();
            results = queryStatement.executeQuery(strGetPartListEntries);
            while(results.next()) {
                int id = results.getInt(1);
                String partName = results.getString(2);
                PartListEntry entry = new PartListEntry(partName, id);
                listEntries.add(entry);
            }
            results.close();
            queryStatement.close();
        } catch (SQLException sqle) {
            sqle.printStackTrace();
        }
        return listEntries;
    }
    
    public Part getPart(int index) {
        Part part = null;
        try {
            stmtGetPart.clearParameters();
            stmtGetPart.setInt(1, index);
            ResultSet result = stmtGetPart.executeQuery();
            if (result.next()) {
                String partName = result.getString("PARTNAME");
                boolean isSpeakingPart = result.getBoolean("ISSPEAKINGPART");
                boolean isSingingPart = result.getBoolean("ISSINGINGPART");
                String notes = result.getString("NOTES");
                int id = result.getInt("ID");
                part = new Part(partName, isSpeakingPart, isSingingPart, notes, id);
            }
        } catch(SQLException sqle) {
            sqle.printStackTrace();
        }
        return part;
    }
    
    private static final String strGetPart =
            "SELECT * FROM APP.PART " +
            "WHERE ID = ?";
    
    private static final String strCreatePart =
            "INSERT INTO APP.PART " +
            "   (PARTNAME, ISSPEAKINGPART, ISSINGINGPART, NOTES) " +
            "VALUES (?, ?, ?, ?)";
    
    
    private static final String strGetPartListEntries =
            "SELECT ID, PARTNAME FROM APP.PART "  +
            "ORDER BY PARTNAME ASC";
    
    private static final String strUpdatePart =
            "UPDATE APP.PART " +
            "SET PARTNAME = ?, " +
            "    ISSPEAKINGPART = ?, " +
            "    ISSINGINGPART = ?, " +
            "    NOTES = ? " +
            "WHERE ID = ?";
    
    private static final String strDeletePart =
            "DELETE FROM APP.PART " +
            "WHERE ID = ?";
    
    public static void main(String[] args) {
        PartDao db = new PartDao();
        db.connect();
        db.disconnect();
    }
}
